﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using NPSoft.Ferrero.DataBridge.Model;
using NPSoft.Ferrero.Framework.DAL;

namespace NPSoft.Ferrero.DataBridge.DAL
{
    public class GeographyUtil : IDAL<Geography>
    {
        public int Add(Geography info)
        {
            throw new NotImplementedException();
        }

        public int Edit(Geography info)
        {
            throw new NotImplementedException();
        }

        public Geography GetById(long id)
        {
            throw new NotImplementedException();
        }

        public Geography GetById(Guid id)
        {
            throw new NotImplementedException();
        }

        public int Remove(Geography info)
        {
            throw new NotImplementedException();
        }

        public System.Data.DataSet Search(StringBuilder condtion)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// SQL连接
        /// </summary>
        private SqlConnection Connection
        {
            get
            {
                return SqlHelper.GetConnection();
            }
        }

        public ICollection<Geography> SearchCollection(StringBuilder condtion)
        {
            ICollection<Geography> infos = new List<Geography>();
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT DST_CD,DISTRICT,DST_NM_EN,SR_EMP_ID,SR_EMP_NM_EN,SR_EMP_NM_CN,
            SR_USR_ID,SV_EMP_ID,SV_EMP_NM_EN,SV_EMP_NM_CN,SV_USR_ID,DSM_EMP_ID,
            DSM_EMP_NM_EN,DSM_EMP_NM_CN,DSM_USR_ID,CITY_CD,CITY,CITY_SAP_CD,CITY_NM_EN,
            CITY_TYPE_LY,CITY_TYPE_TY,CSM_EMP_ID,CSM_EMP_NM_EN,CSM_EMP_NM_CN,CSM_USR_ID,
            CLS_CD,CLUSTER,CLS_NM_EN,CCSM_EMP_ID,CCSM_EMP_NM_EN,CCSM_EMP_NM_CN,CCSM_USR_ID,
            AREA_CD,AREA,AREA_NM_EN,ASM_EMP_ID,ASM_EMP_NM_EN,ASM_EMP_NM_CN,ASM_USR_ID,
            REGION_CD,REGION,REGION_NM_EN,RSM_EMP_ID,RSM_EMP_NM_CN,RSM_EMP_NM_EN,RSM_USR_ID,
            CTY_CD,COUNTRY,CTY_NM_EN FROM vEMP_SALORG_RELATION");

            string sWhere = " where ";
            if (!string.IsNullOrEmpty(condtion.ToString()))
            {

                sql.Append(sWhere + condtion);
            }
            DataSet ds;
            ds = SqlHelper.ExecuteDataset(this.Connection, CommandType.Text, sql.ToString());
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                infos.Add(SetInfo(row));
            }
            return infos;
        }

        public Geography SetInfo(System.Data.DataRow row)
        {
            Geography geography = new Geography();
            geography.DST_CD = SqlHelper.GetString(row, "DST_CD");
            geography.DISTRICT = SqlHelper.GetString(row, "DISTRICT");
            geography.DST_NM_EN = SqlHelper.GetString(row, "DST_NM_EN");
            geography.SR_EMP_ID = SqlHelper.GetString(row, "SR_EMP_ID");
            geography.SR_EMP_NM_EN = SqlHelper.GetString(row, "SR_EMP_NM_EN");
            geography.SR_EMP_NM_CN = SqlHelper.GetString(row, "SR_EMP_NM_CN");
            geography.SR_USR_ID = SqlHelper.GetString(row, "SR_USR_ID");
            geography.SV_EMP_ID = SqlHelper.GetString(row, "SV_EMP_ID");
            geography.SV_EMP_NM_CN = SqlHelper.GetString(row, "SV_EMP_NM_CN");
            geography.SV_EMP_NM_EN = SqlHelper.GetString(row, "SV_EMP_NM_EN");
            geography.SV_USR_ID = SqlHelper.GetString(row, "SV_USR_ID");
            geography.DSM_EMP_ID = SqlHelper.GetString(row, "DSM_EMP_ID");
            geography.DSM_EMP_NM_EN = SqlHelper.GetString(row, "DSM_EMP_NM_EN");
            geography.DSM_EMP_NM_CN = SqlHelper.GetString(row, "DSM_EMP_NM_CN");
            geography.DSM_USR_ID = SqlHelper.GetString(row, "DSM_USR_ID");
            geography.CITY_CD = SqlHelper.GetString(row, "CITY_CD");
            geography.CITY = SqlHelper.GetString(row, "CITY");
            geography.CITY_NM_EN = SqlHelper.GetString(row, "CITY_NM_EN");
            geography.CITY_SAP_CD = SqlHelper.GetString(row, "CITY_SAP_CD");
            geography.CITY_TYPE_LY = SqlHelper.GetString(row, "CITY_TYPE_LY");
            geography.CITY_TYPE_TY = SqlHelper.GetString(row, "CITY_TYPE_TY");
            geography.CSM_EMP_ID = SqlHelper.GetString(row, "CSM_EMP_ID");
            geography.CSM_EMP_NM_CN = SqlHelper.GetString(row, "CSM_EMP_NM_CN");
            geography.CSM_EMP_NM_EN = SqlHelper.GetString(row, "CSM_EMP_NM_EN");
            geography.CSM_USR_ID = SqlHelper.GetString(row, "CSM_USR_ID");
            geography.CLS_CD = SqlHelper.GetString(row, "CLS_CD");
            geography.CLUSTER = SqlHelper.GetString(row, "CLUSTER");
            geography.CLS_NM_EN = SqlHelper.GetString(row, "CLS_NM_EN");
            geography.CCSM_EMP_ID = SqlHelper.GetString(row, "CCSM_EMP_ID");
            geography.CCSM_EMP_NM_CN = SqlHelper.GetString(row, "CCSM_EMP_NM_CN");
            geography.CCSM_EMP_NM_EN = SqlHelper.GetString(row, "CCSM_EMP_NM_EN");
            geography.CCSM_USR_ID = SqlHelper.GetString(row, "CCSM_USR_ID");
            geography.AREA_CD = SqlHelper.GetString(row, "AREA_CD");
            geography.AREA = SqlHelper.GetString(row, "AREA");
            geography.AREA_NM_EN = SqlHelper.GetString(row, "AREA_NM_EN");
            geography.ASM_EMP_ID = SqlHelper.GetString(row, "ASM_EMP_ID");
            geography.ASM_EMP_NM_CN = SqlHelper.GetString(row, "ASM_EMP_NM_CN");
            geography.ASM_EMP_NM_EN = SqlHelper.GetString(row, "ASM_EMP_NM_EN");
            geography.ASM_USR_ID = SqlHelper.GetString(row, "ASM_USR_ID");
            geography.REGION = SqlHelper.GetString(row, "REGION");
            geography.RGN_CD = SqlHelper.GetString(row, "REGION_CD");
            geography.RGN_NM_EN = SqlHelper.GetString(row, "REGION_NM_EN");
            geography.RSM_EMP_ID = SqlHelper.GetString(row, "RSM_EMP_ID");
            geography.RSM_EMP_NM_CN = SqlHelper.GetString(row, "RSM_EMP_NM_CN");
            geography.RSM_EMP_NM_EN = SqlHelper.GetString(row, "RSM_EMP_NM_EN");
            geography.RSM_USR_ID = SqlHelper.GetString(row, "RSM_USR_ID");
            geography.CTY_CD = SqlHelper.GetString(row, "CTY_CD");
            geography.COUNTRY = SqlHelper.GetString(row, "COUNTRY");
            geography.CTY_NM_EN = SqlHelper.GetString(row, "CTY_NM_EN");

            return geography;
        }
    }
}
